﻿using ERPU8WebApi.Until;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
namespace ERPU8WebApi.Dal
{
    //存货档案相关
    public class DALInventory
    {
        SqlHelper s = new SqlHelper();
        //根据ERP代码查存货档案
        public DataTable GetInventory(string cInvStd)
        {
            string sql = string.Format("SELECT * from Inventory where cInvStd='{0}'  AND dEDate IS NULL", cInvStd);
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }
        public DataTable GetInventoryEx(string cInvStd)
        {
            string sql = string.Format("SELECT * from Inventory where cInvStd='{0}'", cInvStd);
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }

        //根据存货编码查存货描述
        public string GetCDefine10(string cinvcode)
        {
            string sql = string.Format(@"SELECT cInvDefine10  FROM Inventory where cInvCode='{0}'", cinvcode);
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            if (dt.Rows.Count > 0)
            {
                if (!string.IsNullOrEmpty(dt.Rows[0]["cInvDefine10"].ToString()))
                {
                    return dt.Rows[0]["cInvDefine10"].ToString();
                }
            }
            return "";
        }
        //查询存货大类
        public DataTable GetInventoryClass()
        {
            string sql = "SELECT * from InventoryClass";
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }

        //查存货档案
        public DataTable GetInventory(string cinvcode, string cInvCCode, string cInvStd, string cinvname)
        {
            int inventoryPageCount = SqlHelper.InventoryPageCount;

            string sql = (@"SELECT * FROM(
                            SELECT ROW_NUMBER() over( ORDER BY cInvCode) AS tableId,* from Inventory where 1=1  and dEDate IS NULL  ");
            if (!string.IsNullOrEmpty(cinvcode))
            {
                sql += string.Format(@"and cinvcode='{0}' ", cinvcode);
            }
            if (!string.IsNullOrEmpty(cInvCCode))
            {
                sql += string.Format(@"and cInvCCode ='{0}' ", cInvCCode);
            }
            if (!string.IsNullOrEmpty(cInvStd))
            {
                sql += string.Format(@"and cInvStd ='{0}' ", cInvStd);
            }
            if (!string.IsNullOrEmpty(cinvname))
            {
                sql += string.Format(@"and cinvname ='{0}' ", cinvname);
            }
            sql += ")t ";

            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }
        public DataTable GetInventory(string cinvcode, string cInvCCode, string cInvStd, string cinvname, int page,ref string sqlr)
        {
            int inventoryPageCount = SqlHelper.InventoryPageCount;

            string sql = (@"SELECT * FROM(
                            SELECT ROW_NUMBER() over( ORDER BY cInvCode) AS tableId,* from Inventory where 1=1  and dEDate IS NULL  ");
            if (!string.IsNullOrEmpty(cinvcode))
            {
                sql += string.Format(@"and cinvcode='{0}' ", cinvcode);
            }
            if (!string.IsNullOrEmpty(cInvCCode))
            {
                sql += string.Format(@"and cInvCCode ='{0}' ", cInvCCode);
            }
            if (!string.IsNullOrEmpty(cInvStd))
            {
                sql += string.Format(@"and cInvStd ='{0}' ", cInvStd);
            }
            if (!string.IsNullOrEmpty(cinvname))
            {
                sql += string.Format(@"and cinvname ='{0}' ", cinvname);
            }
            sql += ")t ";
            if (page==0)
            {
                page = 1;
            }
            int pageIndex = (page - 1) * inventoryPageCount;
            int pageUpper = pageIndex + inventoryPageCount;
           
            sql += string.Format(@" where tableId>{0} and tableId<={1} ", pageIndex, pageUpper);

            sqlr = sql;
            DataTable dt = new DataTable();
            dt = s.ExecuteDataSetSql(sql, 0).Tables[0];
            return dt;
        }
    }
}